"""
添加通知表
用于存储系统通知记录
"""
from tortoise import BaseDBAsyncClient


async def upgrade(db: BaseDBAsyncClient) -> str:
    return """
        CREATE TABLE IF NOT EXISTS `notifications` (
            `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
            `user_id` INT NOT NULL COMMENT '关联用户ID',
            `title` VARCHAR(255) NOT NULL COMMENT '通知标题',
            `content` TEXT NOT NULL COMMENT '通知内容',
            `type` VARCHAR(50) NOT NULL DEFAULT 'system' COMMENT '通知类型：system/success/warning/error',
            `is_read` BOOL NOT NULL DEFAULT 0 COMMENT '是否已读',
            `status` INT NOT NULL DEFAULT 1 COMMENT '状态：0禁用 1启用',
            `extra_data` JSON COMMENT '额外数据',
            `created_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '创建时间',
            `updated_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) COMMENT '更新时间',
            INDEX `idx_notifications_user_id` (`user_id`),
            INDEX `idx_notifications_is_read` (`is_read`),
            INDEX `idx_notifications_created_at` (`created_at`),
            INDEX `idx_notifications_user_id_is_read` (`user_id`, `is_read`),
            CONSTRAINT `fk_notifications_user_id` 
                FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) 
                ON DELETE CASCADE
        ) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT='系统通知表';
    """


async def downgrade(db: BaseDBAsyncClient) -> str:
    return """
        DROP TABLE IF EXISTS `notifications`;
    """


















